package dao;

import utils.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @author 李夏
 * @version 1.0
 */
public class PubInfoMapper {
    //管理员/宿管/学生获取某一个楼栋公共设施总数
    public static Integer getPubOneBuild(Integer buildId) throws SQLException {
        Connection connection = DBUtil.getConnection();
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        String sql = "select count(*) as pub_info_count  \n"+
                "from public  \n"+
                "where delete_flag=0 and build_id=?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, buildId);
        resultSet = preparedStatement.executeQuery();
        Integer count = null;
        if (resultSet.next()) {
            count = resultSet.getInt("pub_info_count");
        }

        DBUtil.close(connection,preparedStatement,resultSet);


        return count;
    }
//管理员获取所有公共设施总数
    public static Integer queryPubAllCount() throws SQLException {
        Connection connection = DBUtil.getConnection();
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        String sql = "select count(*) as total_pub \n"+
                "from public  \n"+
                "where delete_flag=0";
        preparedStatement = connection.prepareStatement(sql);
        resultSet = preparedStatement.executeQuery();
        Integer count = null;
        if (resultSet.next()) {
            count = resultSet.getInt("total_pub");
        }

        DBUtil.close(connection,preparedStatement,resultSet);


        return count;
    }
//管理员/宿管/学生获取某楼栋某一名称的公共设施总数
    public static Integer getPubOneBuildName(Integer buildId, String publicName) throws SQLException {
        Connection connection = DBUtil.getConnection();
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        String sql = "select count(*) as pub_info_count_name  \n"+
                "from public  \n"+
                "where delete_flag=0 and build_id=? and public_name=?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, buildId);
        preparedStatement.setString(2, publicName);

        resultSet = preparedStatement.executeQuery();
        Integer count = null;
        if (resultSet.next()) {
            count = resultSet.getInt("pub_info_count_name");
        }

        DBUtil.close(connection,preparedStatement,resultSet);


        return count;
    }
//管理员获取某名称的所有公共设施总数
    public static Integer getPublicAllName(String publicName) throws SQLException {
        Connection connection = DBUtil.getConnection();
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        String sql = "select count(*) as pub_info_all_name  \n"+
                "from public  \n"+
                "where delete_flag=0 and public_name=?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, publicName);

        resultSet = preparedStatement.executeQuery();
        Integer count = null;
        if (resultSet.next()) {
            count = resultSet.getInt("pub_info_all_name");
        }

        DBUtil.close(connection,preparedStatement,resultSet);


        return count;
    }
}
